package com.iman.common.hibernate;

import java.io.IOException;
import java.io.StringWriter;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.MessageSource;
import org.springframework.context.ResourceLoaderAware;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.util.Assert;
import org.springframework.util.xml.DomUtils;
import org.w3c.dom.Element;
import org.xml.sax.InputSource;

import com.iman.common.support.Page;
import com.iman.common.support.PageMetaData;
import com.iman.common.util.ConfigUtil;
import com.iman.common.util.SpringUtil;
import com.iman.common.util.StringUtils;

import freemarker.cache.NullCacheStorage;
import freemarker.cache.StringTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;

@SuppressWarnings("all")
public class TemplateQLServiceImpl implements TemplateQLService, InitializingBean, ResourceLoaderAware, ApplicationContextAware {

    private MessageSource         messageSource;

    private SessionFactory        sessionFactory;

    protected List                fileNames;

    protected ResourceLoader      resourceLoader = null;

    private NamedParameterJdbcTemplate namedJdbcTemplate;

    private static final boolean  addRowNum      = true;                         //是否自动加上rownum属性。

    private org.springframework.beans.factory.xml.DocumentLoader documentLoader = new org.springframework.beans.factory.xml.DefaultDocumentLoader();
    
    public static final Configuration cfg = new Configuration();
	static {
		cfg.setDefaultEncoding("UTF-8");
		StringTemplateLoader stringLoader = new StringTemplateLoader();
		cfg.setTemplateLoader(stringLoader);
		cfg.setTemplateUpdateDelay(0);
		//cfg.setCacheStorage(new freemarker.cache.MruCacheStorage(20, 250));
		cfg.setCacheStorage(new NullCacheStorage());
	}

	public static final void setStringTemplate(String key, String content) {
		((StringTemplateLoader) cfg.getTemplateLoader()).putTemplate(key, content);
	}

	public static final String getProcessStringByTemplate(String key, Map root) {
		StringWriter writer = new StringWriter();
		try {
			Template template = cfg.getTemplate(key);
			Assert.notNull(template);
			template.process(root, writer);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return writer.toString();
	}

	public static final void checkDuplicateTemplate(String key) {
		Object findTemplateSource = null;
		try {
			findTemplateSource = cfg.getTemplateLoader().findTemplateSource(key);
		} catch (IOException e) {
			e.printStackTrace();
			throw new NullPointerException();
		}
		Assert.isNull(findTemplateSource);
	}
    public void setFileNames(List fileNames) {
        this.fileNames = fileNames;
    }

    public void setResourceLoader(ResourceLoader resourceLoader) {
        this.resourceLoader = resourceLoader;
    }

    public void setNamedJdbcTemplate(NamedParameterJdbcTemplate namedJdbcTemplate) {
        this.namedJdbcTemplate = namedJdbcTemplate;
    }

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.messageSource = (MessageSource) applicationContext.getBean("messageSource");
    }

    public void afterPropertiesSet()  {
        for (int i = 0; i < fileNames.size(); i++) {
            String fileName = ((String) fileNames.get(i)).trim();
            if (resourceLoader instanceof ResourcePatternResolver) {
                try {
                    Resource[] resources = ((ResourcePatternResolver) resourceLoader).getResources(fileName);
                    buildHQLMap(resources);
                } catch (IOException ex) {
                    throw new RuntimeException("Could not resolve sql definition resource pattern [" + fileName + "]", ex);
                }
            } else {
                Resource resource = resourceLoader.getResource(fileName);
                buildHQLMap(new Resource[] { resource });
            }
        }
    }
   
    @Override
    public List findList(String queryName, Map params, int start, int limit)  {
        Query query = findInternal(queryName, params);
        if (start >= 0 && limit > 0) {
            query.setFirstResult(start);
            query.setMaxResults(limit);
        }
        return query.list();
    };

    @Override
    public List findList(String queryName, Map params)  {
        return findList(queryName, params, 0, 0);
    };

    @Override
    public List findSQLList(String queryName, Map params, int start, int limit)  {
        Query query = findSQLInternal(queryName, params);
        if (start >= 0 && limit > 0) {
            query.setFirstResult(start);
            query.setMaxResults(limit);
        }
        return query.list();
    }

    @Override
    public List findSQLList(String queryName, Map params)  {
        return findSQLList(queryName, params, 0, 0);
    }

    @Override
    public Object find(String queryName, Map params)  {
        Query query = findInternal(queryName, params);
        return query.uniqueResult();
    }

    @Override
    public Object findSQL(String queryName, Map params)  {
        Query query = findSQLInternal(queryName, params);
        return query.uniqueResult();
    }

    @Override
    public Object executeUpdate(String queryName, Map params)  {
        Query query = findInternal(queryName, params);
        return query.executeUpdate();
    }

    private Query findInternal(String queryName, Map context) {
        String sql = findSQLByTemplate(queryName, context);
        Query query = sessionFactory.getCurrentSession().createQuery(sql);
        String[] namedParams = query.getNamedParameters();
        setProperties(query, context, namedParams);
        return query;
    }

    private Query findSQLInternal(String queryName, Map context)  {
        String sql = findSQLByTemplate(queryName, context);
        Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
        String[] namedParams = query.getNamedParameters();
        setProperties(query, context, namedParams);
        return query;
    }

    private String findSQLByTemplate(String queryName, Map context) {
    	refreshCache(queryName);
        String sql = getProcessStringByTemplate(queryName, context);
        return sql;
    };
    
    //template, 给继承ReloadTemplateQLServiceImpl用。
    protected void refreshCache(String queryName){
    	
    }

    protected void buildHQLMap(Resource[] resources)  {
        for (int i = 0; i < resources.length; i++) {
            buildHQLMap(resources[i]);
        }
    }

    private void buildHQLMap(Resource resource)  {
        try {
            InputSource inputSource = new InputSource(resource.getInputStream());
            org.w3c.dom.Document doc = this.documentLoader.loadDocument(inputSource, null, null, org.springframework.util.xml.XmlValidationModeDetector.VALIDATION_NONE, false);
            Element root = doc.getDocumentElement();
            List<Element> querys = DomUtils.getChildElements(root);
            for(Element query:querys){
                String queryName = query.getAttribute("name");
                if (StringUtils.isEmpty(queryName)) {
                    throw new Exception("DynamicHibernate Service : name is essential attribute in a <query>.");
                }
                checkDuplicateTemplate(queryName);
                setStringTemplate(queryName,DomUtils.getTextValue(query));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }


    private SortedMap findTextReplacements(StringBuffer sql) {
        TreeMap textReplacements = new TreeMap();
        int startPos = 0;
        while ((startPos = sql.indexOf("{{", startPos)) > -1) {
            int endPos = sql.indexOf("}}", startPos);
            String replacementKey = sql.substring(startPos + 2, endPos);
            sql.replace(startPos, endPos + 2, "");
            textReplacements.put(new Integer(startPos), replacementKey);
        }
        return textReplacements;
    }

    //wenzhi.zhang added 20110604 用map更好
    @Override
    public int executeSQLUpdate(String queryName, Map params)  {
        Query query = findSQLInternal(queryName, params);
        return query.executeUpdate();
    }

    //wenzhi.zhang added 20110604 用map更好
    @Override
    public Page findByPagedSQLQuery(String queryName, Map params, int start, int limit)  {
        Query query = findSQLInternal(queryName, params);

        String countSql = "select count(*) from (" + query.getQueryString() + ") alias";
        Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(countSql);
        String[] namedParams = query.getNamedParameters();
        setProperties(countQuery, params, namedParams);

        Number count = (Number) countQuery.uniqueResult();
        int totalCount = count.intValue();

        if (start >= 0 && limit > 0) {
            query.setFirstResult(start);
            query.setMaxResults(limit);
        }
        return new Page(totalCount, query.list());
    };

    @Override
    public Page findByPagedHQLQuery(String queryName, Map params, int start, int limit){
        Query query = findInternal(queryName, params);
        String countSql = "select count(*) " + removeSelect(removeOrders(query.getQueryString()));
        Query countQuery = sessionFactory.getCurrentSession().createQuery(countSql);

        String[] namedParams = query.getNamedParameters();
        setProperties(countQuery, params, namedParams);

        Number count = (Number) countQuery.uniqueResult();
        int totalCount = count.intValue();

        if (start >= 0 && limit > 0) {
            query.setFirstResult(start);
            query.setMaxResults(limit);
        }
        return new Page(totalCount, query.list());
    };

    private static String removeOrders(String hql) {
        Assert.hasText(hql);
        Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(hql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "");
        }
        m.appendTail(sb);
        return sb.toString();
    };

    private static String removeSelect(String hql) {
        Assert.hasText(hql);
        int beginPos = hql.toLowerCase().indexOf(" from ");
        Assert.isTrue(beginPos != -1, " hql : " + hql + " must has a keyword 'from'");
        return hql.substring(beginPos);
    };

    public <T> Page<T> findByPagedSQLQuery(String queryName, Map params, Class<T> entityClass, int start, int limit)  {
        Query query = findSQLInternal(queryName, params);
        query.setResultTransformer(Transformers.aliasToBean(entityClass));
        String countSql = "select count(*) " + removeSelect(removeOrders(query.getQueryString()));
        Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(countSql);

        String[] namedParams = query.getNamedParameters();
        setProperties(countQuery, params, namedParams);

        Number count = (Number) countQuery.uniqueResult();
        int totalCount = count.intValue();

        if (start >= 0 && limit > 0) {
            query.setFirstResult(start);
            query.setMaxResults(limit);
        }
        return new Page(totalCount, query.list());
    };

    public <T> Page<T> findByPagedSQLMapQuery(String queryName, Map params, int start, int limit)  {
        Query query = findSQLInternal(queryName, params);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        String countSql = "select count(*) " + removeSelect(removeOrders(query.getQueryString()));
        Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(countSql);

        String[] namedParams = query.getNamedParameters();
        setProperties(countQuery, params, namedParams);

        Number count = (Number) countQuery.uniqueResult();
        int totalCount = count.intValue();

        if (start >= 0 && limit > 0) {
            query.setFirstResult(start);
            query.setMaxResults(limit);
        }
        return new Page(totalCount, query.list());
    };

    public <T> List<T> findByPagedSQLQuery(String queryName, Map params, Class<T> entityClass)  {
        Query query = findSQLInternal(queryName, params);
        query.setResultTransformer(Transformers.aliasToBean(entityClass));
        return query.list();
    };//Transformers.ALIAS_TO_ENTITY_MAP

    public <T> List<T> findBySQLMapQuery(String queryName, Map params)  {
        Query query = findSQLInternal(queryName, params);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.list();
    };

    public void setProperties(Query query, Map map, String[] params) throws HibernateException {
        for (int i = 0; i < params.length; i++) {
            String namedParam = params[i];
            final Object object = map.get(namedParam);
            if (object == null) {
            	query.setParameter(namedParam, object);
                continue;
            }
            Class retType = object.getClass();
            if (Collection.class.isAssignableFrom(retType)) {
                query.setParameterList(namedParam, (Collection) object);
            } else if (retType.isArray()) {
                query.setParameterList(namedParam, Arrays.asList((Object[]) object));
            } else {
                query.setParameter(namedParam, object);
            }
        }
    };

    public <T> List<T> findBySQLBeanQuery(String queryName, Map params, Class clazz)  {
        String sql = getProcessStringByTemplate(queryName, params);
        if(ConfigUtil.isOracle()){
        	sql = "select row_.*, rownum rownum_ from (" + sql + ") row_ "; 
        }else{
    	   
        }
        List list = namedJdbcTemplate .query(sql, params, new BeanPropertyRowMapper(clazz));
        return list;
    };

    //TODO to change from oracle ro mysql
    public <T> Page<T> findByPagedSQLBeanQuery(String queryName, Map params, int start, int limit, Class clazz)  {
        
    	String sql = getProcessStringByTemplate(queryName, params);
        String countSql = "select count(*) from(" + sql +") t";
        System.out.println("**********findByPagedSQLBeanQuery*******"+countSql);
        int totalCount = namedJdbcTemplate.queryForInt(countSql, params);

        if (start >= 0 && limit > 0) {
            if(ConfigUtil.isOracle()){
            //select * from (select row_.*, rownum rownum_ from ( select * where foretrippe0_.KTYPE='ForeTripper' ) row_ where rownum <= '40') where rownum_ > '20'
                sql = "select * from (select row_.*, rownum rownum_ from (" + sql + ")row_ where rownum<=" + (start+limit) + ") where rownum_>" + start;
            }else if(ConfigUtil.isMysql()){
            	if(limit==1) {sql = sql + " limit " + start;}
            	else sql = sql + " limit " + start + "," + (/*start +*/limit);
            }
        }
        return new Page(totalCount, namedJdbcTemplate.query(sql, params, new BeanPropertyRowMapper(clazz)));//.getJdbcOperations().query(sql, new BeanPropertyRowMapper(clazz), params));
    };

    //wenzhi.zhang metadata and data all return 
    //queryName is only name in xml (defined sql name)
    public PageMetaData findDataBySQLWithMetaData(String queryName, Map paramMap)  {
        String sql = getProcessStringByTemplate(queryName, paramMap);
        SqlRowSet sqlRowSet = namedJdbcTemplate.queryForRowSet(sql, paramMap);
        List<Map<String, Object>> list = SpringUtil.convert(sqlRowSet);
        return new PageMetaData(list, list.size(), sqlRowSet.getMetaData());
    };

    //paging
    public PageMetaData findDataBySQLWithMetaDataPaging(String queryName, Map paramMap, int start, int limit)  {
        String sql = getProcessStringByTemplate(queryName, paramMap);
        String countSql = "select count(*) " + removeSelect(removeOrders(sql));
        int totalCount = namedJdbcTemplate.queryForInt(countSql, paramMap);
        if (start >= 0 && limit > 0) {
            if(ConfigUtil.isOracle()){
                sql = "select * from (select row_.*, rownum rownum_ from (" + sql + ")row_ where rownum<=" + (start+limit) + ") where rownum_>" + start;
            }else if(ConfigUtil.isMysql()){
                sql = sql + " limit " + start + "," + start +limit;
            }
        }
        SqlRowSet sqlRowSet = namedJdbcTemplate.queryForRowSet(sql, paramMap);
        List<Map<String, Object>> list = SpringUtil.convert(sqlRowSet);
        return new PageMetaData(list, totalCount, sqlRowSet.getMetaData());
    };
    
    /**
     *  Function: findObjectByQuery
     *  功能说明：  根据指定sql返回单个对象
     *	 使用说明：
     *  @author  Lihuai  DateTime Dec 26, 2014-3:07:02 PM
     *	返回类型: T    
     *  @param 
     *  @return T
     */
    @Override
    public <T> T findObjectByQuery(String queryName, Map<String, ?> params, Class clazz){
    	String sql = getProcessStringByTemplate(queryName, params);
    	return (T) namedJdbcTemplate.queryForObject(sql, params, new BeanPropertyRowMapper(clazz));
    }
}
